/* Test */
CREATE TABLE test_image(shop_image CLOB);
drop table test_image;
SELECT * FROM test_image;

/* 게시글 */
select qa_seq,customer_id,inputdate,qa_title from
(select rownum num, qa_seq,
customer_id,
to_char(inputdate, 'YY/MM/DD') inputdate,
qa_title from shop_qa		
order by qa_seq desc) n
where n.num >= 6 and n.num <=10;
select * from product;
select * from shop;

/*shop setting test*/
insert into SHOP(shop_seq) values(SEQ_shop_shop_seq.nextval);
select * from SHOP_BLOG;
select * from shop;
select * from CUSTOMER;
-- 상점 고유시퀀스
	shop_seq number NOT NULL,
	-- 상인아이디
	-- 관리자id: admin
	seller_id varchar2(20) NOT NULL,
	-- 상품 카테고리 키
	-- 
	category char(1) NOT NULL,
	-- 상점명
	name varchar2(50) NOT NULL,
	-- 우편번호
	zipcode varchar2(10) NOT NULL,
	address1 varchar2(300) NOT NULL,
	-- 상세주소
	address2 varchar2(70) NOT NULL,
	-- 상점전화번호
	phone varchar2(15) NOT NULL,
	PRIMARY KEY (shop_seq)

select * from PRODUCT;

/* 상품명을 ~외 몇개 형태로 추가해서 뽑아내야함*/
select o.order_seq, o.result, o.order_type, o.inputdate, detail.sum
	from product_order o,
		(	select d.order_seq, sum(p.product_price*d.quantity) sum,
			from order_detail d, product p
			where p.product_seq = d.product_seq
			group by d.order_seq
		) detail
	where customer_id='abc'
	and o.order_seq = detail.order_seq
	and result!='t';
/* 상점 리스트 쿼리*/
select s.shop_seq,s.name,b.blog_photo,b.blog_bgcolor
		from shop s,shop_blog b;
select * from seller;		
/* 판매자 등록 쿼리 */

/*test data*/
select *	from product where shop_seq=6 order by product_seq desc;
insert into shop_blog(shop_seq) values(6);
select * from category;
select * from seller;
select * from shop;
select * from SHOP_BLOG;
select * from product where shop_seq=6;

alter table SHOP_BLOG add (blog_map number default 0);

select * from SHOP_BLOG;


/*skin table*/
insert into blog_skin values('Cerulean');
insert into blog_skin values('Cosmo');
insert into blog_skin values('Cyborg');
insert into blog_skin values('Darky');
insert into blog_skin values('Flatly');
insert into blog_skin values('Journal');
insert into blog_skin values('Lumen');
insert into blog_skin values('Paper');
insert into blog_skin values('Readable');
insert into blog_skin values('Sandstone');
insert into blog_skin values('Simplex');
insert into blog_skin values('Slate');
insert into blog_skin values('Spacelab');
insert into blog_skin values('Superhero');
insert into blog_skin values('United');
insert into blog_skin values('Yeti');

alter table blog_skin modify(skin varchar2(50) default 'Default');
select * from SHOP;
/*font table*/
insert into blog_font values('바탕체');
insert into blog_font values('nanum');
insert into blog_font values('anew');
insert into blog_font values('궁서체');
/*color table*/
insert into blog_color values('white');
insert into blog_color values('yellow');
insert into blog_color values('teal');
insert into blog_color values('silver');
insert into blog_color values('red');
insert into blog_color values('purple');
insert into blog_color values('orange');
insert into blog_color values('olive');
insert into blog_color values('navy');
insert into blog_color values('maroon');
insert into blog_color values('lime');
insert into blog_color values('gray');
insert into blog_color values('green');
insert into blog_color values('fuchsia');
insert into blog_color values('blue');
insert into blog_color values('black');
insert into blog_color values('aqua');
/*카테고리 정보*/
insert into category values('f','과일류');
insert into category values('v','채소류');
insert into category values('i','생선류');
insert into category values('m','육류');
insert into category values('g','식료품류');
insert into category values('h','건강용품');
insert into category values('a','패션');
insert into category values('l','생활용품');
insert into category values('e','기타');


update shop_blog set blog_style
select SEQ_product_product_seq.nextval from dual; 
select * from PRODUCT;
select * from shop_blog;
select * from shop;